const { NewsModel, PublishNewsModel, ReleaseListModel } = require('../models');
const { getDb } = require('../database');
const { underscoreToCamel } = require('../utils/transform');

class NewsService {
  constructor() {}

  /**
   * 获取新闻列表（分页）
   * @param {number} page - 页码
   * @param {number} pageSize - 每页条数
   * @returns {Promise<Object>} 新闻列表和总数
   */
  async getNewsList(page = 1, pageSize = 10, filters = {}) {
    const db = await getDb();
    const offset = (page - 1) * pageSize;

    const whereClauses = [];
    const params = [];

    // keywords: abstract_real | news_title 模糊匹配
    if (filters.keywords) {
      whereClauses.push('(abstract_real LIKE ? OR news_title LIKE ?)');
      const kw = `%${filters.keywords}%`;
      params.push(kw, kw);
    }

    // classReal: 支持单值或多值
    if (filters.classRealList && filters.classRealList.length > 0) {
      const list = filters.classRealList.filter((n) => [1,2,3,4,5].includes(n));
      if (list.length > 0) {
        whereClauses.push(`class_real IN (${list.map(() => '?').join(',')})`);
        params.push(...list);
      }
    }

    // addMode: 1 | 0
    if (filters.addMode === 0 || filters.addMode === 1) {
      whereClauses.push('add_mode = ?');
      params.push(filters.addMode);
    }

    // timeRange: [createTimeFrom, createTimeTo]
    if (filters.timeRange && (filters.timeRange[0] || filters.timeRange[1])) {
      const [from, to] = filters.timeRange;
      if (from) {
        whereClauses.push('create_time >= ?');
        params.push(from);
      }
      if (to) {
        whereClauses.push('create_time <= ?');
        params.push(to);
      }
    }

    // isPublished: 0 | 1
    if (filters.isPublished === 0 || filters.isPublished === 1) {
      whereClauses.push('is_published = ?');
      params.push(filters.isPublished);
    }

    let query = 'SELECT * FROM news';
    let countQuery = 'SELECT COUNT(*) as count FROM news';
    if (whereClauses.length > 0) {
      const whereSql = ' WHERE ' + whereClauses.join(' AND ');
      query += whereSql;
      countQuery += whereSql;
    }

    // sortby: 0 => DESC, 1 => ASC (create_time)
    if (filters.sortby === 0) {
      query += ' ORDER BY create_time DESC';
    } else if (filters.sortby === 1) {
      query += ' ORDER BY create_time ASC';
    }

    query += ' LIMIT ? OFFSET ?';
    const dataParams = params.concat([pageSize, offset]);

    const news = await db.all(query, dataParams);
    const total = await db.get(countQuery, params);

    return {
      records: underscoreToCamel(news),
      total: total.count
    };
  }

  /**
   * 创建新闻
   * @param {Object} newsData - 新闻数据
   * @returns {Promise<number>} 新创建的新闻ID
   */
  async createNews(newsData) {
    const {
      abstractReal,
      classReal,
      newsTitle,
      title,
      newsSource,
      newsTime,
      coverImage,
      isSelect,
      classModel,
      addMode = 1,
      siteName,
      opPerson,
      createTime = new Date().toISOString().replace('T', ' ').substring(0, 19),
      pubTime,
      isPublished = 0,
      markContent,
      sourceUrl
    } = newsData;
    
    // 如果没有提供title，使用newsTitle作为title
    const finalTitle = title || newsTitle;
    
    const db = await getDb();
    const result = await db.run(
      'INSERT INTO news (abstract_real, class_real, news_title, title, news_source, news_time, cover_image, is_select, class_model, add_mode, site_name, op_person, create_time, pub_time, is_published, mark_content, source_url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [abstractReal, classReal, newsTitle, finalTitle, newsSource, newsTime, coverImage, isSelect || 0, classModel, addMode, siteName, opPerson, createTime, pubTime, isPublished, markContent, sourceUrl]
    );
    
    return result.lastID;
  }

  /**
   * 更新新闻
   * @param {number} id - 新闻ID
   * @param {Object} newsData - 新闻数据
   * @returns {Promise<boolean>} 是否更新成功
   */
  async updateNews(id, newsData) {
    const allowedMap = {
      abstractReal: 'abstract_real',
      classReal: 'class_real',
      newsTitle: 'news_title',
      title: 'title',
      newsSource: 'news_source',
      newsTime: 'news_time',
      coverImage: 'cover_image',
      isSelect: 'is_select',
      classModel: 'class_model',
      addMode: 'add_mode',
      siteName: 'site_name',
      opPerson: 'op_person',
      createTime: 'create_time',
      pubTime: 'pub_time',
      isPublished: 'is_published',
      markContent: 'mark_content',
      sourceUrl: 'source_url'
    };
    
    const setClauses = [];
    const params = [];
    
    for (const [key, column] of Object.entries(allowedMap)) {
      if (newsData[key] !== undefined) {
        setClauses.push(`${column} = ?`);
        params.push(newsData[key]);
      }
    }
    
    if (setClauses.length === 0) {
      return false;
    }
    
    // 始终更新更新时间
    setClauses.push('updated_at = CURRENT_TIMESTAMP');
    
    const db = await getDb();
    const result = await db.run(
      `UPDATE news SET ${setClauses.join(', ')} WHERE id = ?`,
      [...params, id]
    );
    
    // 若成功，且本次操作涉及选中（加入/移除release_list），同步写入/创建release_list的op_person
    if (result && result.changes > 0 && newsData.isSelect !== undefined) {
      try {
        if (Number(newsData.isSelect) === 1) {
          // 标记选中：确保存在记录，并写入操作人
          const up = await db.run(
            'UPDATE release_list SET is_select = 1, op_person = COALESCE(?, op_person) WHERE news_id = ?',
            [newsData.opPerson || null, id]
          );
          if (!up || up.changes === 0) {
            const row = await db.get('SELECT news_title FROM news WHERE id = ?', [id]);
            const title = row?.news_title || String(id);
            await db.run(
              'INSERT INTO release_list (news_id, title, op_person, is_select, order_news) VALUES (?, ?, ?, 1, 0)',
              [id, title, newsData.opPerson || null]
            );
          }
        } else {
          // 取消选中：删除release_list记录（简化逻辑，选中列表仅包含当前选中项）
          await db.run('DELETE FROM release_list WHERE news_id = ?', [id]);
        }
      } catch (e) {
        // 不中断主流程
      }
    }
    
    return result.changes > 0;
  }

  /**
   * 根据ID获取新闻详情
   * @param {number} id - 新闻ID
   * @returns {Promise<Object|null>} 新闻详情
   */
  async getNewsById(id) {
    const db = await getDb();
    const row = await db.get('SELECT * FROM news WHERE id = ?', [id]);
    return row ? underscoreToCamel(row) : null;
  }

  /**
   * 获取各类别新闻数量
   * @returns {Promise<Object>} 各类别新闻数量统计
   */
  async getNewsCountByClass() {
    const db = await getDb();
    
    const macroEnvironment = await db.get('SELECT COUNT(*) as count FROM news WHERE class_real = 1');
    const industryTrends = await db.get('SELECT COUNT(*) as count FROM news WHERE class_real = 2');
    const supplyChainDynamics = await db.get('SELECT COUNT(*) as count FROM news WHERE class_real = 3');
    const internationalEnergy = await db.get('SELECT COUNT(*) as count FROM news WHERE class_real = 4');
    const other = await db.get('SELECT COUNT(*) as count FROM news WHERE class_real > 4');
    
    return {
      macroEnvironment: macroEnvironment.count,
      industryTrends: industryTrends.count,
      supplyChainDynamics: supplyChainDynamics.count,
      internationalEnergy: internationalEnergy.count,
      other: other.count
    };
  }

  /**
   * 立即发布新闻
   * @param {Object} publishData - 发布数据
   * @returns {Promise<number>} 新创建的发布ID
   */
  async publishNewsNow(publishData) {
    const { newsId, newsTitle, classReal, classModel, abstractReal, newsSource, newsTime, opPerson, coverImage } = publishData;
    
    const db = await getDb();
    
    // 开始事务
    await db.run('BEGIN TRANSACTION');
    
    try {
      // 创建发布记录（立即发布）
      const result = await db.run(
        'INSERT INTO publish_news (news_id, news_title, class_real, class_model, abstract_real, news_source, news_time, create_time, publish_time, op_person, pub_state, cover_image) VALUES (?, ?, ?, ?, ?, ?, ?, datetime(), datetime(), ?, 1, ?)',
        [newsId, newsTitle, classReal, classModel, abstractReal, newsSource, newsTime, opPerson, coverImage]
      );
      
      // 发布成功后：同步news状态与时间
      await db.run('UPDATE news SET is_select = 0, pub_time = datetime(), is_published = 1 WHERE id = ?', [newsId]);
      
      await db.run('COMMIT TRANSACTION');
      
      return result.lastID;
    } catch (error) {
      await db.run('ROLLBACK TRANSACTION');
      throw error;
    }
  }

  /**
   * 定时发布新闻
   * @param {Object} publishData - 发布数据
   * @returns {Promise<number>} 新创建的发布ID
   */
  async scheduleNewsPublish(publishData) {
    const { newsId, newsTitle, classReal, classModel, abstractReal, newsSource, newsTime, waitPubTime, opPerson, coverImage } = publishData;
    
    const db = await getDb();
    const result = await db.run(
      'INSERT INTO publish_news (news_id, news_title, class_real, class_model, abstract_real, news_source, news_time, create_time, wait_pub_time, op_person, pub_state, cover_image) VALUES (?, ?, ?, ?, ?, ?, ?, datetime(), ?, ?, 0, ?)',
      [newsId, newsTitle, classReal, classModel, abstractReal, newsSource, newsTime, waitPubTime, opPerson, coverImage]
    );
    
    return result.lastID;
  }

  /**
   * 更新发布定时
   * @param {number} id - 发布ID
   * @param {string} waitPubTime - 等待发布时间
   * @returns {Promise<boolean>} 是否更新成功
   */
  async updatePublishSchedule(id, waitPubTime) {
    const db = await getDb();
    const result = await db.run(
      'UPDATE publish_news SET wait_pub_time = ?, pub_state = 0 WHERE id = ?',
      [waitPubTime, id]
    );
    
    return result.changes > 0;
  }

  /**
   * 获取发布列表
   * @param {number} page - 页码
   * @param {number} pageSize - 每页条数
   * @param {number} pubState - 发布状态（可选）
   * @returns {Promise<Object>} 发布列表和总数
   */
  async getPublishList(page = 1, pageSize = 10, pubState = undefined) {
    const db = await getDb();
    const offset = (page - 1) * pageSize;
    
    let query = 'SELECT * FROM publish_news';
    const params = [];
    
    if (pubState !== undefined) {
      query += ' WHERE pub_state = ?';
      params.push(pubState);
    }
    
    query += ' LIMIT ? OFFSET ?';
    params.push(pageSize, offset);
    
    const publishNews = await db.all(query, params);
    const totalQuery = 'SELECT COUNT(*) as count FROM publish_news' + (pubState !== undefined ? ' WHERE pub_state = ?' : '');
    const totalParams = pubState !== undefined ? [pubState] : [];
    const total = await db.get(totalQuery, totalParams);

    // 追加当期发布批次信息（记录当期发布的新闻ID）
    const latestIssue = await db.get('SELECT issue_no, news_ids FROM publish_issue ORDER BY issue_no DESC LIMIT 1');
    const currentIssueNo = latestIssue?.issue_no || null;
    const currentIssueNewsIds = latestIssue?.news_ids ? latestIssue.news_ids.split(',').map((s) => Number(s)).filter((n) => !Number.isNaN(n)) : [];
    
    return {
      records: underscoreToCamel(publishNews),
      total: total.count,
      currentIssueNo,
      currentIssueNewsIds
    };
  }

  /** 新增：获取issue期刊列表（分页） */
  async getIssueList(page = 1, pageSize = 10) {
    const db = await getDb();
    const offset = (page - 1) * pageSize;

    const totalRow = await db.get('SELECT COUNT(*) as count FROM publish_issue');
    const latestIssue = await db.get('SELECT MAX(issue_no) as maxNo FROM publish_issue');
    const rows = await db.all(
      'SELECT issue_no, start_date, end_date, news_ids, title, cover_image, description, publish_time, publisher FROM publish_issue ORDER BY issue_no DESC LIMIT ? OFFSET ?',
      [pageSize, offset]
    );

    return {
      records: rows.map((r) => ({
        issueNo: r.issue_no,
        startDate: r.start_date,
        endDate: r.end_date,
        newsIds: (r.news_ids || '').split(',').filter(Boolean).map((n) => Number(n)),
        title: r.title || null,
        coverImage: r.cover_image || null,
        description: r.description || null,
        publishTime: r.publish_time || null,
        publisher: r.publisher || null
      })),
      total: totalRow.count,
      currentIssueNo: latestIssue?.maxNo || 0
    };
  }

  /**
   * 获取issue对应的新闻列表（分页）
   * @param {number} issueNo
   * @param {number} page - 页码
   * @param {number} pageSize - 每页条数
   * @returns {Promise<Object>} 新闻列表和总数
   */
  async getIssueNews(issueNo) {
    const db = await getDb();
    const row = await db.get(
      'SELECT news_ids, title, cover_image, description, publish_time, publisher FROM publish_issue WHERE issue_no = ?',
      [issueNo]
    );
    if (!row) {
      return {
        records: [],
        total: 0,
        issueTitle: null,
        issueCoverImage: null,
        issueDescription: null,
        issuePublishTime: null,
        issuePublisher: null
      };
    }
    const idsOrder = (row.news_ids || '')
      .split(',')
      .map((n) => Number(n))
      .filter((n) => !Number.isNaN(n));
    const total = idsOrder.length;
    if (total === 0) {
      return {
        records: [],
        total,
        issueTitle: row.title || null,
        issueCoverImage: row.cover_image || null,
        issueDescription: row.description || null,
        issuePublishTime: row.publish_time || null,
        issuePublisher: row.publisher || null
      };
    }

    const placeholders = idsOrder.map(() => '?').join(',');
    const newsRows = await db.all(`SELECT * FROM news WHERE id IN (${placeholders})`, idsOrder);
    const byId = new Map(newsRows.map((r) => [r.id, r]));
    const ordered = idsOrder.map((id) => byId.get(id)).filter(Boolean);

    return {
      records: underscoreToCamel(ordered),
      total,
      issueTitle: row.title || null,
      issueCoverImage: row.cover_image || null,
      issueDescription: row.description || null,
      issuePublishTime: row.publish_time || null,
      issuePublisher: row.publisher || null
    };
  }

  /**
   * 获取发布新闻详情
   * @param {number} id - 发布ID
   * @returns {Promise<Object|null>} 发布新闻详情
   */
  async getPublishNewsById(id) {
    const db = await getDb();
    const publishNews = await db.get('SELECT * FROM publish_news WHERE id = ?', [id]);
    
    return publishNews ? underscoreToCamel(publishNews) : null;
  }

  /**
   * 获取选中列表（从news表中查询is_select=1的数据）
   * @returns {Promise<Object>} 选中的新闻列表和总数
   */
  async getSelectedNewsList() {
    const db = await getDb();
    const selectedNews = await db.all(
      `SELECT n.*, rl.order_news AS order_news, rl.op_person AS rl_op_person
       FROM news n
       LEFT JOIN release_list rl ON rl.news_id = n.id AND rl.is_select = 1
       WHERE n.is_select = 1
       ORDER BY n.created_at DESC`
    );
    
    const rows = underscoreToCamel(selectedNews).map((item) => ({
      ...item,
      opPerson: item.opPerson || item.rlOpPerson || null
    }));
    
    return {
      list: rows,
      total: selectedNews.length
    };
  }

  /**
   * 给选中列表排序
   * @param {Array} sortedList - 排序后的列表
   * @returns {Promise<boolean>} 是否排序成功
   */
  async sortReleaseList(sortedList, operator = null) {
    const db = await getDb();
    
    // 开始事务
    await db.run('BEGIN TRANSACTION');
    
    try {
      for (const item of sortedList) {
        if (item.id && item.orderNews !== undefined) {
          const updateRes = await db.run(
            'UPDATE release_list SET order_news = ?, op_person = COALESCE(?, op_person) WHERE news_id = ?',
            [item.orderNews, operator || null, item.id]
          );
          if (!updateRes || updateRes.changes === 0) {
            // 不存在则插入一条最小记录（仅用于保存排序），title为news.news_title，其他字段置空
            const row = await db.get('SELECT news_title FROM news WHERE id = ?', [item.id]);
            const title = row?.news_title || String(item.id);
            await db.run(
              'INSERT INTO release_list (news_id, abstract_real, class_real, title, site_name, create_time, op_person, is_select, order_news) VALUES (?, NULL, NULL, ?, NULL, NULL, ?, 1, ?)',
              [item.id, title, operator || null, item.orderNews]
            );
          }
        }
      }
      await db.run('COMMIT TRANSACTION');
      
      return true;
    } catch (error) {
      await db.run('ROLLBACK TRANSACTION');
      throw error;
    }
  }

  /**
   * 删除新闻
   * @param {number} id - 新闻ID
   * @returns {Promise<boolean>} 是否删除成功
   */
  async deleteNews(ids) {
    const db = await getDb();
    await db.run('BEGIN TRANSACTION');
    try {
      for (const id of ids) {
        await db.run('DELETE FROM news WHERE id = ?', [id]);
      }
      await db.run('COMMIT TRANSACTION');
      return true;
    } catch (error) {
      await db.run('ROLLBACK TRANSACTION');
      throw error;
    }
  }

  /**
   * 获取发布模板信息
   * @returns {Promise<Object>} 发布模板信息
   */
  async getPublishTemplate() {
    const db = await getDb();
    // 使用发布批次表计算No（每期+1）
    const total = await db.get('SELECT COUNT(*) as count FROM publish_issue');
    const nextNo = (total?.count || 0) + 1;
    const noStr = String(nextNo).padStart(2, '0');

    const now = new Date();
    const preview = new Date(now);
    preview.setDate(now.getDate() - 7);

    const fmt = (d) => {
      const y = d.getFullYear();
      const m = String(d.getMonth() + 1).padStart(2, '0');
      const day = String(d.getDate()).padStart(2, '0');
      return `${y}.${m}.${day}`;
    };

    const template = `战略微头条 ｜ (No.${noStr})  ${fmt(preview)}-${fmt(now)}`;
    const styles = '默认样式';

    return {
      template,
      styles
    };
  }

  async publishReleaseListAndClear(list = [], waitPubTime = undefined, issueMeta = {}) {
    const db = await getDb();
    await db.run('BEGIN TRANSACTION');
    try {
      // 2) 读取所有选中新闻（不依赖 release_list 排序）
      const rows = await db.all(
        `SELECT n.id as news_id,
                n.news_title, n.class_real, n.abstract_real, n.news_source, n.news_time, n.cover_image
         FROM news n
         WHERE n.is_select = 1
         ORDER BY n.created_at DESC`
      );

      // 上限校验：每期刊最多 10 条
      if (rows.length > 10) {
        throw new Error(`单期刊发布的新闻条数不能超过10（当前：${rows.length}）`);
      }

      // 2.1) 计算发布批次号，并记录当期新闻ID列表
      const latestIssue = await db.get('SELECT MAX(issue_no) as maxNo FROM publish_issue');
      const issueNo = (latestIssue?.maxNo || 0) + 1;
      const newsIds = rows.map((r) => r.news_id).filter((id) => id != null);
      // 冗余保护：再次防止超过 10
      if (newsIds.length > 10) {
        throw new Error(`单期刊发布的新闻条数不能超过10（当前：${newsIds.length}）`);
      }
      const now = new Date();
      const preview = new Date(now);
      preview.setDate(now.getDate() - 7);
      const fmtDate = (d) => {
        const y = d.getFullYear();
        const m = String(d.getMonth() + 1).padStart(2, '0');
        const day = String(d.getDate()).padStart(2, '0');
        return `${y}.${m}.${day}`;
      };
      const fmtDateTime = (d) => {
        const y = d.getFullYear();
        const m = String(d.getMonth() + 1).padStart(2, '0');
        const day = String(d.getDate()).padStart(2, '0');
        const hh = String(d.getHours()).padStart(2, '0');
        const mm = String(d.getMinutes()).padStart(2, '0');
        const ss = String(d.getSeconds()).padStart(2, '0');
        return `${y}-${m}-${day} ${hh}:${mm}:${ss}`;
      };
      const title = issueMeta?.title || null;
      const coverImage = issueMeta?.coverImage || null;
      const description = issueMeta?.description || null;
      const publisher = issueMeta?.publisher || null;
      const publishTime = issueMeta?.publishTime || fmtDateTime(now);

      await db.run(
        'INSERT INTO publish_issue (issue_no, start_date, end_date, news_ids, title, cover_image, description, publish_time, publisher) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
        [issueNo, fmtDate(preview), fmtDate(now), newsIds.join(','), title, coverImage, description, publishTime, publisher]
      );

      let publishedCount = 0;
      const nowInsert = 'INSERT INTO publish_news (news_id, news_title, class_real, class_model, abstract_real, news_source, news_time, create_time, publish_time, op_person, pub_state, cover_image, issue_no) VALUES (?, ?, ?, ?, ?, ?, ?, datetime(), datetime(), ?, 1, ?, ?)';
      const waitInsert = 'INSERT INTO publish_news (news_id, news_title, class_real, class_model, abstract_real, news_source, news_time, create_time, wait_pub_time, op_person, pub_state, cover_image, issue_no) VALUES (?, ?, ?, ?, ?, ?, ?, datetime(), ?, ?, 0, ?, ?)';

      for (const r of rows) {
        const newsId = r.news_id;
        const newsTitle = r.news_title || '';
        const classReal = r.class_real ?? null;
        const classModel = null; // 无法从表中确定，置空
        const abstractReal = r.abstract_real ?? null;
        const newsSource = r.news_source ?? null;
        const newsTime = r.news_time ?? null;
        const opPerson = publisher; // 使用期刊发布人作为批次内条目的发布人
        const coverImageItem = r.cover_image ?? null;

        if (waitPubTime) {
          await db.run(waitInsert, [newsId, newsTitle, classReal, classModel, abstractReal, newsSource, newsTime, waitPubTime, opPerson, coverImageItem, issueNo]);
        } else {
          await db.run(nowInsert, [newsId, newsTitle, classReal, classModel, abstractReal, newsSource, newsTime, opPerson, coverImageItem, issueNo]);
        }
        // 发布后重置选中状态
        await db.run('UPDATE news SET is_select = 0 WHERE id = ?', [newsId]);
        // 立即发布：同步news.pub_time与is_published
        if (!waitPubTime) {
          await db.run('UPDATE news SET pub_time = datetime(), is_published = 1 WHERE id = ?', [newsId]);
        }
        publishedCount++;
      }

      // 3) 清空 release_list
      await db.run('DELETE FROM release_list');

      await db.run('COMMIT TRANSACTION');
      return { publishedCount, issueNo, currentIssueNewsIds: newsIds };
    } catch (error) {
      await db.run('ROLLBACK TRANSACTION');
      throw error;
    }
  }
}

module.exports = NewsService;